<?php

//This script has been used to move spell modification from spell_template to spell_template_override
//table db_spell_8606 is my dbc table. Some fields are excluded because my extracted dbc data is wrong for floats.

error_reporting( E_ALL );
ini_set('display_errors', 1);

$servername = "localhost";
$username = "root";
$password = "canard";
$db = "world";

$pdo = new PDO("mysql:host=$servername;dbname=$db", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql_fields = "entry, category, dispel, mechanic, attributes, attributesEx, attributesEx2, attributesEx3, attributesEx4, attributesEx5, attributesEx6, stances, stancesNot, targets, targetCreatureType, requiresSpellFocus, facingCasterFlags, casterAuraState, targetAuraState, casterAuraStateNot, targetAuraStateNot, castingTimeIndex, recoveryTime, categoryRecoveryTime, interruptFlags, auraInterruptFlags, channelInterruptFlags, procFlags, procChance, procCharges, maxLevel, baseLevel, spellLevel, durationIndex, powerType, manaCost, manaCostPerlevel, manaPerSecond, manaPerSecondPerLevel, rangeIndex, stackAmount, totem1, totem2, reagent1, reagent2, reagent3, reagent4, reagent5, reagent6, reagent7, reagent8, reagentCount1, reagentCount2, reagentCount3, reagentCount4, reagentCount5, reagentCount6, reagentCount7, reagentCount8, equippedItemClass, equippedItemSubClassMask, equippedItemInventoryTypeMask, effect1, effect2, effect3, effectDieSides1, effectDieSides2, effectDieSides3, effectBaseDice1, effectBaseDice2, effectBaseDice3, effectDicePerLevel1, effectDicePerLevel2, effectDicePerLevel3, effectBasePoints1, effectBasePoints2, effectBasePoints3, effectMechanic1, effectMechanic2, effectMechanic3, effectImplicitTargetA1, effectImplicitTargetA2, effectImplicitTargetA3, effectImplicitTargetB1, effectImplicitTargetB2, effectImplicitTargetB3, effectRadiusIndex1, effectRadiusIndex2, effectRadiusIndex3, effectApplyAuraName1, effectApplyAuraName2, effectApplyAuraName3, effectAmplitude1, effectAmplitude2, effectAmplitude3, effectChainTarget1, effectChainTarget2, effectChainTarget3, effectMiscValue1, effectMiscValue2, effectMiscValue3, effectMiscValueB1, effectMiscValueB2, effectMiscValueB3, effectTriggerSpell1, effectTriggerSpell2, effectTriggerSpell3, effectPointsPerComboPoint1, effectPointsPerComboPoint2, effectPointsPerComboPoint3, spellVisual, spellIconID, activeIconID, manaCostPercentage, startRecoveryCategory, startRecoveryTime, maxTargetLevel, spellFamilyName, spellFamilyFlags, maxAffectedTargets, dmgClass, preventionType, totemCategory1, totemCategory2, areaId, schoolMask";

$stmt = $pdo->prepare("SELECT {$sql_fields} FROM spell_template;");
//echo $sql . "<br/>";

$count = 0;
$new_values = array(array());
$old_values = array(array());
unset($new_values[0]);
unset($old_values[0]);

if (!$stmt->execute()) 
	die("horribly");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) 
{
	$entry = $row["entry"];
	$res = $pdo->query("SELECT {$sql_fields} FROM db_spell_8606 WHERE entry = {$entry}", PDO::FETCH_ASSOC);
	$vanilla_row = $res->fetch();
	if(!$vanilla_row)
		continue;
	//compare each field
	foreach($row as $key => $field)
	{
		if($field != $vanilla_row[$key])
		{
			$new_values[$entry][$key] = $field;
			$old_values[$entry][$key] = $vanilla_row[$key];
			$count++;
		}
	}
}

echo "<pre style='position: absolute; left: 400px'>";
echo "Old values <br/>";
print_r($old_values);
echo "</pre>";

echo "<pre>";
echo "<br/>New values <br/>";
print_r($new_values);
echo "</pre>";

echo "<p>$count</p>";

foreach($new_values as $spellId => $new_val_spell)
{
	$fields = "`entry`";
	foreach($new_val_spell as $key => $new_val_field)
		$fields .= ', `' . $key . '`';
		
	$fields .= ', `Comment`';
	
	$values = $spellId;
	foreach($new_val_spell as $key => $new_val_field)
		$values .= ', ' . $new_val_field;
		
	$values .= ', "Autogenerated from diff between spell_template and dbc. Do not hesitate to remove if you\'re working on this spell."';
	
	$sql = "INSERT INTO spell_template_override (${fields}) VALUES (${values});";
	echo $sql . '<br/>';
}

echo "<br/>";

foreach($old_values as $spellId => $old_val_spell)
{
	$fields = '';
	$first = true;
	foreach($old_val_spell as $key => $old_val_field)
	{
		if(!$first)
			$fields .= ', ';
		else
			$first = false;
		
		$fields .= "`$key` = $old_val_field";
	}
		
	$sql = "UPDATE spell_template SET ${fields} WHERE `entry` = {$spellId};";
	echo $sql . '<br/>';
}

echo "<br/>";

foreach($new_values as $spellId => $new_val_spell)
	echo  $spellId . ',';